After Aggregating

Question:

Start Quiz:

#
# Find the one food that is eaten by only one animal.
#
# The animals table has columns (name, species, birthdate) for each individual.
# The diet table has columns (species, food) for each food that a species eats.
#

QUERY = '''
select ...
'''

User's Answer:

(Note: The answer done by the user is not guaranteed to be correct)

query.py

#
# Find the one food that is eaten by only one animal.
#
# The animals table has columns (name, species, birthdate) for each individual.
# The diet table has columns (species, food) for each food that a species eats.
#

QUERY = '''
select food, count(animals.name) as num
       from diet join animals 
       on diet.species = animals.species
       group by food
       having num = 1
'''

Solution:

INSTRUCTOR NOTE:

The having clause works like the where clause, but it applies after group by aggregations take place. The syntax is like this:

select columns from tables group by column having condition ;

Usually, at least one of the columns will be an aggregate function such as count, max, or sum on one of the tables' columns. In order to apply having to an aggregated column, you'll want to give it a name using as. For instance, if you had a table of items sold in a store, and you wanted to find all the items that have sold more than five units, you could use:

select name, count(*) as num from sales having num > 5;

You can have a select statement that uses only where, or only group by, or group by and having, or where and group by, or all three of them!

But it doesn't usually make sense to use having without group by.

If you use both where and having, the where condition will filter the rows that are going into the aggregation, and the having condition will filter the rows that come out of it.

You can read more about having here:

http://www.postgresql.org/docs/9.4/static/sql-select.html#SQL-HAVING

There are a few different ways to solve this, but here's one of them:

select food, count(animals.name) as num
       from diet join animals 
       on diet.species = animals.species
       group by food
       having num = 1

And here is another:

select food, count(animals.name) as num
       from diet, animals 
       where diet.species = animals.species
       group by food
       having num = 1